import sqlite3
import mysql.connector
import pymysql
from PyQt6.QtCore import QObject
from utils.Logger import LogPrint

class DatabaseWrapper(QObject):

    def __init__(self,use_sqlite=True):
        super().__init__()
        self.use_sqlite = use_sqlite
        self.sqlite_connection = None
        self.sqlite_cursor = None
        self.mysql_connection = None
        self.mysql_cursor = None

        self.toggle_database(use_sqlite)

    def toggle_database(self, use_sqlite):
        if use_sqlite is not None:
            self.use_sqlite = use_sqlite
        LogPrint.debug('self.use_sqlite',self.use_sqlite)
        if self.use_sqlite:
            if not self.sqlite_connection:
                self.sqlite_connection = sqlite3.connect('sqlite_database.db')
                self.sqlite_cursor = self.sqlite_connection.cursor()
        else:
            if not self.mysql_connection:
                # self.mysql_connection = mysql.connector.connect(
                #     host='127.0.0.1',
                #     port=3306,
                #     user='admin',
                #     password='1q2w3e4r5t',
                #     database='db_tally_book'
                # )
                self.mysql_connection=pymysql.connect(host='127.0.0.1',
                             port=3306,
                             user='admin',
                             password='1q2w3e4r5t',
                             database='db_tally_book')
                self.mysql_cursor = self.mysql_connection.cursor()

    def execute_query(self, query):
        if self.use_sqlite:
            if not self.sqlite_cursor:
                raise Exception("SQLite cursor not initialized")
            self.sqlite_cursor.execute(query)
            self.sqlite_connection.commit()
            return self.sqlite_cursor.fetchall()
        else:
            if not self.mysql_cursor:
                raise Exception("MySQL cursor not initialized")
            self.mysql_cursor.execute(query)
            self.mysql_connection.commit()
            return self.mysql_cursor.fetchall()
        
    def execute_insert(self, query):
        if self.use_sqlite:
            if not self.sqlite_cursor:
                raise Exception("SQLite cursor not initialized")
            self.sqlite_cursor.execute(query)
            self.sqlite_connection.commit()
            return self.sqlite_cursor.fetchall()
        else:
            if not self.mysql_cursor:
                raise Exception("MySQL cursor not initialized")
            res=self.mysql_cursor.execute(query)
            self.mysql_connection.commit()
            return res

    def insert(self, table, data):
        columns = ', '.join(data.keys())
        values = ', '.join(f"'{value}'" for value in data.values())
        query = f"INSERT INTO {table} ({columns}) VALUES ({values})"
        LogPrint.debug(query)
        return self.execute_insert(query)

    def delete(self, table, condition):
        query = f"DELETE FROM {table} WHERE {condition}"
        return self.execute_insert(query)

    def update(self, table, data, condition):
        set_clause = ', '.join(f"{key} = '{value}'" for key, value in data.items())
        query = f"UPDATE {table} SET {set_clause} WHERE {condition}"
        LogPrint.debug(query)
        return self.execute_insert(query)

    def select(self, table, columns='*', condition=None,group =None, order=None,limit=None):
        if condition:
            query = f"SELECT {columns} FROM {table} WHERE {condition}"
        else:
            query = f"SELECT {columns} FROM {table}"
        if group:
            query += f" group by {group}"
        if order:
            query += f" order by {order}"
        if limit:
            query += f" limit {limit}"
        LogPrint.debug(query)
        return self.execute_query(query)
    
if __name__ == "__main__":
    database_wrapper = DatabaseWrapper()

    # 切换到 SQLite
    database_wrapper.toggle_database(True)
    database_wrapper.insert('your_table', {'column1': 'value1', 'column2': 'value2'})
    results = database_wrapper.select('your_table')
    print(results)

    # 切换到 MySQL
    database_wrapper.toggle_database(False)
    database_wrapper.insert('your_table', {'column1': 'value3', 'column2': 'value4'})
    results = database_wrapper.select('your_table')
    print(results)